const express = require("express")
const router = express.Router()
const conn = require("../db/mysql")

//图片上传
const upload = require("../upload")

//图片上传
router.post("/upload", upload.single("file"), (req, res) => {
    // 需要返回图片的访问地址    域名+上传文件夹+文件名
    // const url = "http://localhost:3000/upload/" + req.file.filename
    const url = req.file.filename
    res.json({ url })
})

//添加新班级 
router.post("/class", (req, res) => {
    //接收参数
    let { className, classroom, ht, startDate } = req.body;
    let sql = "insert into classInfo (className, classroom, ht,startDate) values (?,?,?,?)"
    conn.query(sql, [className, classroom, ht, startDate], (err, result) => {
        if (err) {
            console.log('增加失败');
            return;
        }
        let data;
        if (result.affectedRows === 1) {
            data = {
                code: 0,
                msg: '添加成功'
            }
        } else {
            data = {
                code: 1,
                msg: '添加失败'
            }
        }
        res.send(data)
    })
})

// 查询班级
router.get("/class", (req, res) => {
    let { page } = req.query
    let sql;
    let data;
    if (page){
        let start = (page - 1) * 6;
        sql = `select * from classinfo order by classId desc limit ${start},6`
    }else{
        sql = `select * from classinfo order by classId desc`
    }

    function task1() {
        return new Promise((resolve, reject) => {
            conn.query(sql, function (err, result) {
                if (err) {
                    reject('查询失败')
                } else {
                    if (result.length) {
                        data = {
                            code: 0,
                            list: result
                        }
                    } else {
                        data = {
                            code: 1,
                            msg: '没有结果 '
                        }
                    }
                    resolve(data)
                }
            })
        })
    }

    task1()
        .then( data => {
            let sql1 = "select count(classId) as total  from classinfo";
            conn.query(sql1, function (err, result) {
                if (err) {
                    console.log('查询数据库失败');
                } else {
                    data.total = result[0].total
                    res.send(data)
                }
            })
        })
        .catch(err => {

        })

})

//删除班级
router.delete("/class", (req, res) => {
    //接收参数
    let { id } = req.query;
    let sql = "delete from classinfo where classId = ?"
    conn.query(sql, [id], (err, result) => {
        if (err) {
            console.log('删除失败');
            return;
        }
        let data;
        if (result.affectedRows === 1) {
            data = {
                code: 0,
                msg: '删除成功'
            }
        } else {
            data = {
                code: 1,
                msg: '删除失败'
            }
        }
        res.send(data)
    })
})

//修改班级
router.put("/class", (req, res) => {
    //接收参数

    let { classId, className, classroom, ht, startDate } = req.body;
    let sql = "update classinfo set className=?,classroom=?,ht=?, startDate=? where classId = ?"
    conn.query(sql, [className, classroom, ht, startDate, classId], (err, result) => {
        if (err) {
            console.log('服务器异常');
            return;
        }
        let data;
        if (result.affectedRows === 1) {
            data = {
                code: 0,
                msg: '更新成功'
            }
        } else {
            data = {
                code: 1,
                msg: '更新失败'
            }
        }
        res.send(data)
    })
})


//新生报名 
router.post("/student", (req, res) => {
    //接收参数
    let { name, age, gender, pay, payStatus,classId,imgUrl } = req.body;
    let sql = "insert into stuInfo (name, age, gender, pay, payStatus,classId,imgUrl) values (?,?,?,?,?,?,?)"
    conn.query(sql, [name, age, gender, pay, payStatus,classId,imgUrl], (err, result) => {
        if (err) {
            console.log('增加失败');
            return;
        }
        let data;
        if (result.affectedRows === 1) {
            data = {
                code: 0,
                msg: '添加成功'
            }
        } else {
            data = {
                code: 1,
                msg: '添加失败'
            }
        }
        res.send(data)
    })
})


// 查询学生
router.get("/student", (req, res) => {
    let { page,classId } = req.query
    let pageSize = 3;
    let start = (page - 1) * pageSize;
    let sql;
    let data;
    if (classId){
        // 选择指定班级的学生信息
        sql = `select * from stuInfo,classInfo where stuInfo.classId=classInfo.classId and stuInfo.classId = ${classId} order by stuId desc limit ${start},${pageSize}`
    }else{
        //选择所有班级的学生信息
        sql = `select * from stuInfo,classInfo where stuInfo.classId=classInfo.classId order by stuId desc limit ${start},${pageSize}`
    }

    function task1() {
        return new Promise((resolve, reject) => {
            conn.query(sql, function (err, result) {
                if (err) {
                    reject('查询失败1')
                } else {
                    if (result.length) {
                        data = {
                            code: 0,
                            list: result
                        }
                    } else {
                        data = {
                            code: 1,
                            msg: '没有结果 '
                        }
                    }
                    resolve(data)
                }
            })
        })
    }

    task1()
        .then( data => {
            let sql1;
            if (classId){
               sql1 = `select count(stuId) as total  from stuInfo where classId=${classId}`;
            }else{
               sql1 = "select count(stuId) as total  from stuInfo";
            }
            conn.query(sql1, function (err, result) {
                if (err) {
                    console.log('查询数据库失败');
                } else {
                    data.total = result[0].total
                    res.send(data)
                }
            })
        })
        .catch(err => {

        })

})

module.exports = router